package databasePackage;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/**
 * This class is in charge of creating, connecting, sending / receiving of data
 * to a database. It will create a database if isn't created before or just 
 * connect and update/receive all data, it can also request queries from Database. 
 * @author Marcelo Korn & Oleg Ruchlis
 **/
public class DatabaseUrl {
    
    /** Creates a new instance of DatabaseUrl(default constructor)
    *   It will build an statistics database, it will call the constructor which
    *   receives name of database.
    **/
    public DatabaseUrl() throws IOException {
    }    
    
    /**
     * Constructor
     * @param pages - string that has the name of database.
     * @throws IOException when can't load the properties file.
     * @throws ClassNotFoundException when receives a wrong class name.
     * related to the connection.
     */
    public DatabaseUrl(String pages) throws IOException, ClassNotFoundException{
         this.dbName = pages;
         //set all conf for the Database(drivers and creation
         setDBSystemDir();
         try{
            dbProperties = loadDBProperties();
         }
         catch(IOException ex){
             throw ex;
         }
         String driverName = dbProperties.getProperty("derby.driver"); 
         
         try{
             loadDatabaseDriver(driverName);
         }catch(ClassNotFoundException ex){
             throw ex;
         }
         //if didn't exist, so create it
         if(!dbExists())
         {
            createDatabase();
         }
        
    }
    
    /**
    * This method will save record into the database.
    * @param entry Contains all data of a user to be inserted to the DB.
    * @throws Exception re-throws the SQL error message with number of error
    * related to the connection.
    * @return int - id of the new entry. -1 case of faulty entry.
    */
    public int saveRecord(ListEntry entry)throws SQLException{
        int id = -1;
        //if there is no database key, retrun -1
        if (entry.username == null)
        {
            return id;
        }
        //connection saving data
        try {
            stmtSaveNewRecord.clearParameters();
            stmtSaveNewRecord.setString(1,entry.firstName); 
            stmtSaveNewRecord.setString(2,entry.lastName);
            stmtSaveNewRecord.setString(3,entry.emailAddress);
            stmtSaveNewRecord.setString(4,entry.username);
            stmtSaveNewRecord.setString(5,entry.password);
            stmtSaveNewRecord.executeUpdate();
            ResultSet results = stmtSaveNewRecord.getGeneratedKeys();
            if (results.next()) {
                id = results.getInt(1);
            }            
        } catch(SQLException sqle) {
            throw new SQLException("Sqle error number : " + sqle.getErrorCode()+
                               "\nError type: "         + sqle.getMessage());           
        }
        return id;
    }
    
    /**
     * Making sure there will be no duplication checking if the entry provided
     * can be inserted to the DB
     * @param wantedEntry contains the details of the new wanted entry
     * @return boolean indicating if the new entry can be inserted.
     */
    public boolean isAvailable(ListEntry wantedEntry)
    {
        List<ListEntry> listEntries = null;

        try
        {
            // receiving a list of all entries in the database
            listEntries = getListEntries();
        }

        catch (SQLException ex) 
        {
            return(true);
        }
        
        // going through all users in the DB.
        for(ListEntry entry : listEntries)
        {
            // checking for a match
            if(entry.username.equals(wantedEntry.username))
                return(false);
        }
        
        // no match was found.
        return(true);
    }
    /**
     * This method creates the database, return bool if database was created 
     * @throws Exception re-throws the sql error message with number of error
     * related to the connection.
     * @return boolean indicating success of the creation.
     */
    private boolean createDatabase() {
        boolean bCreated = false;
        Connection dbConnection1 = null;
        //get database to connect
        String dbUrl = getDatabaseUrl();        
        dbProperties.put("create", "true");        
        try {
            dbConnection1 = DriverManager.getConnection(dbUrl, dbProperties);
            bCreated = createTables(dbConnection1);
        } catch (SQLException ex) {}
        //remove the creation preperties(was created already)
        dbProperties.remove("create");
        return bCreated;
    }
    
     /**
     * This  Method check if database exists, getting location and file
     * directory returning boolean if was exists.
     * @return boolean indicating existence of the DB.
     */
    private boolean dbExists() {
        boolean bExists = false;
        String dbLocation = getDatabaseLocation();
        File dbFileDir = new File(dbLocation);
        if (dbFileDir.exists()) {
            bExists = true;
        }
        return bExists;
    }
    
    /**
    * Get database location 
    * @return String with the location of the DB.
    */
    public String getDatabaseLocation() {
        String dbLocation = System.getProperty("derby.system.home")
                                                    + "\\" + dbName;
        return dbLocation;
    }
    
    /**
    * Set directory of system database
    */
    private void setDBSystemDir() {
        // decide on the db system directory
        String userHomeDir = System.getProperty("user.home", ".");
        //String systemDir = userHomeDir ;
        System.setProperty("derby.system.home", userHomeDir);
        
        // create the db system directory
        File fileSystemDir = new File(userHomeDir);
        fileSystemDir.mkdir();
    }   
    
    /**
     * Load all properties from database
     * @throws Exception type message and local error.
     * @return instance of Properties generated when DB was created.
     */
    private Properties loadDBProperties() throws IOException {
        InputStream dbPropInputStream = null;
        dbPropInputStream = DatabaseUrl.class.getResourceAsStream("Configuration.properties");
        dbProperties = new Properties();
        try {
            dbProperties.load(dbPropInputStream);
        } catch (IOException ex) {
           throw ex;
        }
        return dbProperties;
    }     
    
    /**
     * This method load the driver to connect to the database
     * @param driverName string with the driver name that will
     * be used in the connection
     * @throws ClassNotFoundException with kind of error if didnt load driver .
     */
    private void loadDatabaseDriver(String driverName)throws ClassNotFoundException {
        // load Derby driver
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException ex) {
            throw ex;
        }
        
    }    
    
    /**
     * This method will create the tables of ImgTagconter
     * @param dbConnection
     * @return boolean if table was created
     * @throws Exception with kind of error if didn't create tables . 
     */
    private boolean createTables(Connection dbConnection) {
        boolean bCreatedTables = false;
        Statement statement = null;
        try {
            statement = dbConnection.createStatement();
            statement.execute(strCreateAddressTable);
            bCreatedTables = true;
        } catch (SQLException ex) {
        }        
        return bCreatedTables;
     }     
    
    /**
     * This method will connect to the database creating statements to queries
     * get/set and update for example.
     * @throws SQLException If didn't connect.
     */
    public void connect() throws SQLException{
        String dbUrl = getDatabaseUrl();
        try {
            dbConnection = DriverManager.getConnection(dbUrl, dbProperties);
            stmtSaveNewRecord = dbConnection.prepareStatement(strSaveUser, Statement.RETURN_GENERATED_KEYS);
            isConnected = dbConnection != null;
        } catch (SQLException ex) {
            //if got exeption and the reason is that url already exist
            //reconect to data base anyway, because it's created 
            if(ex.getErrorCode() == 30000)
            {
                createTables(dbConnection);
                connect();
            }
            else
                throw new SQLException("Couldn't connect to database.");
        }
    }
    
    /**
     * This method will disconnect from the database
     * @throws Exception with kind of error if didn't disconnected
     **/
    public void disconnect(){
        if(isConnected) {
            String dbUrl = getDatabaseUrl();
            dbProperties.put("shutdown", "true");
            try {
                DriverManager.getConnection(dbUrl, dbProperties);
            } catch (SQLException ex) {
            }
        }
    }
    
    /**
     * This method will get the database URL
     * @return String with database url
     **/
    public String getDatabaseUrl() {
        String dbUrl = dbProperties.getProperty("derby.url") + dbName;
        return dbUrl;
    }
    
    /**
     * This method build list and fill it with all entries
     * @return list with all entries
     * @throws Exception with kind of error if didn't get entries
     **/
    public List<ListEntry> getListEntries() throws SQLException
    {
        List<ListEntry> listEntries = new ArrayList<ListEntry>();
        Statement queryStatement = null;
        ResultSet results = null;
        
        try 
        {
            queryStatement = dbConnection.createStatement();
            
            results = queryStatement.executeQuery(strGetListEntries);
            
            while(results.next()) {
                ListEntry entry = new ListEntry(results.getString(1),
                                                results.getString(2),
                                                results.getString(3),
                                                results.getString(4),
                                                results.getString(5));
                listEntries.add(entry);
            }
            
        } 
        catch (SQLException sqle) 
        {
            throw sqle;    
        }
        
        //return list of entries
        return listEntries;
    }
     
    /**
    * Database name
    */     
    private String dbName; 

    /**
    * Connection statement
    */
    private Connection dbConnection;

    /**
    * Properties of database
    */
    private Properties dbProperties;

    /**
    * boolean which represents if DB is connected
    */
    private boolean isConnected;

    /**
    * Statement of database to save new record
    */
    private PreparedStatement stmtSaveNewRecord;

    /**Strings from database that will be queried   
    * creation.
    */
    private static final String strCreateAddressTable =
                "create table APP.PAGES (" +
                "    FIRSTNAME      VARCHAR(256)," +
                "    LASTNAME       VARCHAR(256)," +
                "    EMAILADDRESS   VARCHAR(256)," +
                "    USERNAME       VARCHAR(256) NOT NULL PRIMARY KEY," +
                "    PASSWORD       VARCHAR(256)" +
                ")";
    /**Strings from database that will be queried   
    * get some User.
    */
    private static final String strGetUser =
                "SELECT * FROM APP.PAGES " +
                "WHERE USERNAME = ?";
    
    /**Strings from database that will be queried   
    * save.
    */
    private static final String strSaveUser =
                "INSERT INTO APP.PAGES " +
                "(FIRSTNAME, LASTNAME, EMAILADDRESS, USERNAME, PASSWORD) " +
                "VALUES (?, ?, ?, ?, ?)";

    /**Strings from database that will be queried   
    * delete.
    */
    private static final String strDeleteUser =
                "DELETE FROM APP.PAGES " +
                "WHERE USERNAME = ?";
    
    /**Strings from database that will be queried   
    * get entries.
    */
    private static final String strGetListEntries =
                "SELECT FIRSTNAME, LASTNAME, EMAILADDRESS, USERNAME, PASSWORD FROM APP.PAGES ";
}
